package com.hy.dao;

import com.hy.bean.Card;
import com.hy.bean.UmsUser;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class UserDao {
    private QueryRunner qr = new QueryRunner();

    public List<UmsUser> selectFollowMe(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where id in (select user_id from follow where user1_id = ? and tombstone = '0')";

        BeanListHandler<UmsUser> blh = new BeanListHandler<>(UmsUser.class);

        List<UmsUser> user = qr.query(conn, sql, blh,id);
        DbUtils.close(conn);
        return user;
    }

    public UmsUser selectBy(String username) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where user_name = ?";
        BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
        BeanHandler<UmsUser> bhl = new BeanHandler<>(UmsUser.class,brp);
        UmsUser user = qr.query(conn, sql, bhl, username);
        DbUtils.close(conn);
        return user;
    }
    public boolean addUser(String username,String password)throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "insert into ums_user(id,user_name,password,create_time) values(null,?,?,?)";

        SimpleDateFormat formatter= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();

        int count = qr.update(conn, sql, username, password,formatter.format(date));

        DbUtils.close(conn);
        return count > 0 ? true:false;
    }

    public String selectByid(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select user_status from ums_user where id = ?";
        UmsUser query = qr.query(conn, sql, new BeanHandler<>(UmsUser.class), id);
        DbUtils.close(conn);
        return (String) query.getUser_status();
    }

    // 修改用户状态为违规(禁止登录)
    public int updateUserStatusByid(Integer uid,String userStatus) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = null;
        if (userStatus.equals("2")){// 永久封号
            sql = "update ums_user set tombstone = '1' where id = ?";
        } else if (userStatus.equals("0")) {
            sql = "update ums_user set user_status = '1' where id = ?";// 禁言
        } else if (userStatus.equals("1")) {
            sql = "update ums_user set user_status = '2' where id = ?";// 违规
        }
        int update = qr.update(conn, sql, uid);
        DbUtils.close(conn);
        return update;
    }

    // 改成正常用户
    public int updateUserStatusByuid(Integer uid,Connection conn) throws Exception {
//        Connection conn = DBManager.getConn();
//        conn.setAutoCommit(false);
        String sql = "update ums_user set user_status = '0' where id = ?";
        int update = qr.update(conn, sql, uid);
//        DbUtils.close(conn);
        return update;
    }

    public List<Card> selectSy() throws Exception {

        //1,2
        Connection conn = DBManager.getConn();

        //3
        String sql = "select * from card";
        BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());
        BeanListHandler<Card> blh = new BeanListHandler<>(Card.class,brp);

        List<Card> cardList = qr.query(conn, sql, blh);

        DbUtils.close(conn);
        return  cardList;
    }

    public List<UmsUser> selectFollowUesr(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where id in (select user1_id from follow where user_id = ? and tombstone = '0')";

        BeanListHandler<UmsUser> blh = new BeanListHandler<>(UmsUser.class);

        List<UmsUser> user = qr.query(conn, sql, blh,id);
        DbUtils.close(conn);
        return user;
    }

    public List<Map<String, Object>> selectByAttention(Integer id)throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from ums_user where id not in(select user1_id from follow where user_id = ? and tombstone = '0') and id != ?";

        List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler(), id,id);

        DbUtils.close(conn);
        return mapList;
    }

    public static void main(String[] args) throws Exception {
        UserDao userDao = new UserDao();
        List<Card> cards = userDao.selectSy();
        System.out.println(cards);
    }
}
